﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using MySql.Data.MySqlClient;
using dataSendModel.Util;
using System.Configuration;

namespace dataSendDal.Util
{
    public class SqlDBHelper : IDisposable
    {
        string connStr = ConfigurationManager.AppSettings["connStr"];
        private MySqlConnection conn;
        public MySqlConnection Conn
        {
            get
            {
                try
                {

                    if (conn == null)
                    {
                        conn = new MySqlConnection(connStr);
                    }
                    if (conn.State == ConnectionState.Broken)
                    {
                        conn.Close();
                        conn.Open();
                    }
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                }
                catch (Exception)
                {

                }

                return conn;
            }

        }


        #region 刷新连接
        public void RefleshConnection()
        {

            if (Conn.State == ConnectionState.Closed)
                Conn.Open();
            if (Conn.State == ConnectionState.Broken)
            {
                Conn.Close();
                Conn.Open();
            }
        }
        #endregion

        #region  关闭连接
        public void CloseConnection()
        {
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
        }
        #endregion

        #region  执行SQL语句
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <returns>返回受影响行数(string)</returns>
        public int ExecuteNoQuery(string SqlStr)
        {
            RefleshConnection();
            MySqlCommand cmd = new MySqlCommand(SqlStr, Conn);
            cmd.CommandTimeout = 300;
            return cmd.ExecuteNonQuery();
        }
        #endregion

        #region 获取DataTable数据集
        /// <summary>
        /// 通过SQL语句，获取DataTable数据集
        /// </summary>
        /// <param name="SqlStr">传入的SQL语句（string）</param>
        /// <returns></returns>
        public DataTable GetTable(string SqlStr)
        {
            RefleshConnection();
            MySqlDataAdapter dap = new MySqlDataAdapter(SqlStr, Conn);
            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];

        }
        #endregion

        #region 获取DataTable数据集（带参）
        /// <summary>
        /// 通过SQL语句，获取DataTable数据集
        /// </summary>
        /// <param name="SqlStr">传入的SQL语句（string）</param>
        /// <param name="parameter">传入的SQL参数</param>
        /// <returns></returns>
        public DataTable GetTable(string SqlStr, params MySqlParameter[] parameter)
        {
            RefleshConnection();
            MySqlCommand mc = new MySqlCommand(SqlStr, Conn);
            mc.Parameters.AddRange(parameter);
            MySqlDataAdapter dap = new MySqlDataAdapter(mc);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            return ds.Tables[0];
        }
        #endregion

        #region 获取DataTable数据集（带参）
        /// <summary>
        /// 通过SQL语句，获取DataTable数据集
        /// </summary>
        /// <param name="SqlStr">sql</param>
        /// <param name="param">参数集合</param>
        /// <returns></returns>
        public DataTable GetTable(string SqlStr, Dictionary<string, object> dic)
        {
            RefleshConnection();
            MySqlCommand comm = new MySqlCommand(SqlStr, Conn);
            MySqlParameter[] parameters = null;
            if (dic != null && dic.Count > 0)
            {
                parameters = new MySqlParameter[dic.Count];
                int index = 0;
                foreach (string key in dic.Keys)
                {
                    parameters[index] = new MySqlParameter(key, dic[key]);
                    index++;
                }
                comm.Parameters.AddRange(parameters);
            }

            MySqlDataAdapter dap = new MySqlDataAdapter(comm);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            return ds.Tables[0];
        }
        #endregion

        #region 获取DataSet数据集
        /// <summary>
        /// 获取DataSet数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet getDataSet(string sql)
        {
            RefleshConnection();
            MySqlCommand com = new MySqlCommand(sql, Conn);
            MySqlDataAdapter ad = new MySqlDataAdapter(com);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            return ds;
        }
        #endregion

        #region 带参获取DataSet数据集
        /// <summary>
        /// 带参获取DataSet数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet getDataSet(string sql, params MySqlParameter[] para)
        {
            RefleshConnection();
            MySqlCommand com = new MySqlCommand(sql, Conn);
            com.Parameters.AddRange(para);
            MySqlDataAdapter ad = new MySqlDataAdapter(com);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            return ds;
        }
        #endregion

        #region  通过存储过程处理的事例
        public bool BeginReturnDatabase()
        {
            RefleshConnection();
            string proName = "wyc";
            MySqlDataAdapter mySqlDA = new MySqlDataAdapter();
            //调用存储过程
            mySqlDA.SelectCommand = new MySqlCommand("protestc", Conn);
            //这一句表示SqlDataAdapter调用执行的是一个存储过程。如果没有这一句，该存储过程将不会被执行。 
            mySqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
            //向存储过程传入一个参数。
            mySqlDA.SelectCommand.Parameters.Add("@ProName", MySqlDbType.Text, 50);

            mySqlDA.SelectCommand.Parameters["@ProName"].Value = proName;


            Console.WriteLine("end backup produce");
            return true;
        }
        #endregion

        #region  获取DataReader数据
        /// <summary>
        /// 得到的数据为只读
        /// </summary>
        /// <param name="SqlStr">传入的SQL语句（string）</param>
        /// <returns>返回SQL数据（SqlDataReader）</returns>
        public MySqlDataReader GetReader(string SqlStr)
        {
            RefleshConnection();
            MySqlCommand cmd = new MySqlCommand(SqlStr, Conn);
            return cmd.ExecuteReader();
        }
        #endregion

        #region 获取DataReader数据(带参)
        /// <summary>
        /// 得到的数据为只读
        /// </summary>
        /// <param name="SqlStr">传入的SQL语句（string）</param>
        /// <param name="paras">传入的参数数组（SqlParameter【】）</param>
        /// <returns>返回SQL数据（SqlDataReader）</returns>
        public MySqlDataReader GetReader(string SqlStr, params MySqlParameter[] parameter)
        {
            RefleshConnection();
            MySqlCommand cmd = new MySqlCommand(SqlStr, Conn);
            cmd.Parameters.AddRange(parameter);
            return cmd.ExecuteReader();
        }
        #endregion

        #region 获取DataReader数据(带参)
        /// <summary>
        /// 通过SQL语句，获取DataReader数据集
        /// </summary>
        /// <param name="SqlStr">sql</param>
        /// <param name="param">参数集合</param>
        /// <returns></returns>
        public MySqlDataReader GetReader(string SqlStr, Dictionary<string, object> param)
        {
            RefleshConnection();
            MySqlCommand mc = new MySqlCommand(SqlStr, Conn);
            MySqlParameter[] parameters = null;
            if (param != null && param.Count > 0)
            {
                parameters = new MySqlParameter[param.Count];
                int index = 0;
                foreach (string key in param.Keys)
                {
                    parameters[index] = new MySqlParameter(key, param[key]);
                    index++;
                }
                mc.Parameters.AddRange(parameters);
            }
            return mc.ExecuteReader();
        }
        #endregion

        #region  执行带参数的SQL
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="dic">参数集合</param>
        /// <returns></returns>
        public int ExecuteNoQuery(string SqlStr, Dictionary<string, object> dic)
        {
            RefleshConnection();
            MySqlCommand comm = new MySqlCommand(SqlStr, Conn);
            MySqlParameter[] parameters = null;
            if (dic != null && dic.Count > 0)
            {
                parameters = new MySqlParameter[dic.Count];
                int index = 0;
                foreach (string key in dic.Keys)
                {
                    parameters[index] = new MySqlParameter(key, dic[key]);
                    index++;
                }
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <returns>返回受影响行数(string)</returns>
        public int ExecuteNoQuery(string SqlStr, params MySqlParameter[] parameter)
        {
            RefleshConnection();

            MySqlCommand comm = new MySqlCommand(SqlStr, Conn);

            comm.Parameters.AddRange(parameter);

            return comm.ExecuteNonQuery();
        }

        /// <summary>
        /// 带参查询
        /// </summary>
        /// <param name="SqlStr">where条件为in的sql语句</param>
        /// <param name="ids">参数集合</param>
        /// <returns></returns>
        public int ExecuteNoQuery(string SqlStr, List<int> ids)
        {
            RefleshConnection();
            string param = string.Empty;
            string value = string.Empty;
            MySqlParameter[] msp = new MySqlParameter[ids.Count];

            char index = 'a';
            for (int i = 0; i < ids.Count; i++)
            {
                value = "?" + index + ids[i];
                param += value + ",";
                msp[i] = new MySqlParameter(value, ids[i]);
                index++;
            }

            param = param.Substring(0, param.Length - 1);

            SqlStr = SqlStr.Replace(Constants.PARAM, param);

            MySqlCommand comm = new MySqlCommand(SqlStr, Conn);

            comm.Parameters.AddRange(msp);

            return comm.ExecuteNonQuery();
        }

        #endregion  执行SQL语句

        #region 执行SQL，返回查询的值
        /// <summary>
        /// 查询返回值
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <returns></returns>
        public object ExecuteScalar(string SqlStr)
        {
            RefleshConnection();
            MySqlCommand comm = new MySqlCommand(SqlStr, Conn);
            return comm.ExecuteScalar();
        }

        /// <summary>
        /// 带参查询返回值
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <param name="dic"></param>
        /// <returns></returns>
        public object ExecuteScalar(string SqlStr, Dictionary<string, object> dic)
        {
            RefleshConnection();
            MySqlCommand comm = new MySqlCommand(SqlStr, Conn);
            MySqlParameter[] parameters = null;
            if (dic != null && dic.Count > 0)
            {
                parameters = new MySqlParameter[dic.Count];
                int index = 0;
                foreach (string key in dic.Keys)
                {
                    parameters[index] = new MySqlParameter(key, dic[key]);
                    index++;
                }
                comm.Parameters.AddRange(parameters);
            }
            return comm.ExecuteScalar();
        }
        #endregion

        public void Dispose()
        {
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
        }

    }
}
